package com.situ.edu.dao.impl;

import com.situ.edu.dao.ITeacherStuDao;
import com.situ.edu.pojo.entity.Elective;
import com.situ.edu.pojo.entity.Student;
import com.situ.edu.pojo.entity.Teacher;
import com.situ.edu.pojo.query.StudentQuery;
import com.situ.edu.pojo.vo.StudentVO;
import com.situ.edu.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;

public class TeacherStuDaoImpl implements ITeacherStuDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<StudentVO> selectByPage(StudentQuery studentQuery) {
        /*String sql = "SELECT s.id,s.`name`,s.age,s.gender,s.`status`,s.xi_id,c.`name` AS courseName,e.student_id,e.course_id,e.scores,c.gredit,x.`name` AS xiName,s.create_time,s.update_time\n" +
                "FROM student AS s INNER JOIN elective AS e\n" +
                "ON s.id=e.student_id INNER JOIN course AS c\n" +
                "ON e.course_id=c.id INNER JOIN xi AS x\n" +
                "ON c.xi_id=x.id where 1=1";*/
        String sql = "SELECT s.id,s.name,s.age,s.gender,s.`status`,c.id AS courseId,c.name AS courseName,e.scores,e.click_count,c.gredit,x.name AS xiName,e.update_time AS updateTime\n" +
                "FROM `user` AS u INNER JOIN teacher AS te \n" +
                "ON u.id=te.user_id INNER JOIN taught AS ta\n" +
                "ON te.id=ta.teacher_id INNER JOIN course AS c\n" +
                "ON ta.course_id=c.id INNER JOIN elective AS e\n" +
                "ON c.id=e.course_id INNER JOIN student AS s\n" +
                "ON e.student_id=s.id INNER JOIN xi AS x\n" +
                "ON s.xi_id=x.id";
        List<Object> queryList = new ArrayList<>();
        //搜索学生姓名
        if (!ObjectUtils.isEmpty(studentQuery.getName())){
            sql += " and s.name like ?";
            queryList.add("%" + studentQuery.getName() + "%");
        }
        //所在系
        if (!ObjectUtils.isEmpty(studentQuery.getXiId())){
            sql += " and s.xi_id=?";
            queryList.add(studentQuery.getXiId());
        }

        sql += " limit ?,?";
        int offset =(studentQuery.getPage() - 1)*studentQuery.getLimit();
        queryList.add(offset);
        queryList.add(studentQuery.getLimit());
        return template.query(sql,
                new BeanPropertyRowMapper<>(StudentVO.class),queryList.toArray());
    }

    @Override
    public int selectTotalCount(StudentQuery studentQuery) {
        /*String sql = "SELECT count(*)\n" +
                "FROM student AS s INNER JOIN elective AS e\n" +
                "ON s.id=e.student_id INNER JOIN course AS c\n" +
                "ON e.course_id=c.id INNER JOIN xi AS x\n" +
                "ON c.xi_id=x.id where 1=1"; */
        String sql = "SELECT count(*)\n" +
                "FROM `user` AS u INNER JOIN teacher AS te \n" +
                "ON u.id=te.user_id INNER JOIN taught AS ta\n" +
                "ON te.id=ta.teacher_id INNER JOIN course AS c\n" +
                "ON ta.course_id=c.id INNER JOIN elective AS e\n" +
                "ON c.id=e.course_id INNER JOIN student AS s\n" +
                "ON e.student_id=s.id INNER JOIN xi AS x\n" +
                "ON s.xi_id=x.id";

        List<Object> queryList = new ArrayList<>();
        //搜索学生姓名
        if (!ObjectUtils.isEmpty(studentQuery.getName())){
            sql += " and s.name like ?";
            queryList.add("%" + studentQuery.getName() + "%");
        }
        //所在系
        if (!ObjectUtils.isEmpty(studentQuery.getXiId())){
            sql += " and s.xi_id=?";
            queryList.add(studentQuery.getXiId());
        }

        return template.queryForObject(sql,Integer.class,queryList.toArray());
    }



    @Override
    public void update(Elective elective) {
        template.update("update elective set scores=?,click_count=click_count+1 where student_id=? AND course_id=?",
                elective.getScores(),elective.getStudentId(),elective.getCourseId());
    }

    @Override
    public Elective selectByStuId(int id, int courseId) {
        return template.queryForObject("select * from elective where student_id=? AND course_id=? ",
                new BeanPropertyRowMapper<>(Elective.class),id,courseId);
    }

    @Override
    public Teacher selectByTeaId(int userId) {
        return template.queryForObject("select * from teacher where user_id=?",
                new BeanPropertyRowMapper<>(Teacher.class),userId);
    }
}
